#! /bin/bash
export LANG=zh_CN.UTF-8

if [[ $1 == "" ]];then
   DT=`date -d '-1 days' "+%Y-%m-%d"`
else
   DT=$1
fi

--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
-----------------拉链导入-----------------
--抽取新增数据
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${DT}' as dt from t_shop_order where 1=1 and (create_date_time between '${DT} 00:00:00' and '${DT} 23:59:59') or (update_date_time between '${DT} 00:00:00' and '${DT} 23:59:59') and  \$CONDITIONS" \
--hcatalog-database edu_ods \
--hcatalog-table customer_relationship \
-m 1

--创建临时表 临时表结构和最终拉链表结构一样
DROP TABLE IF EXISTS edu_dwd.dwd_fact_customer_relationship_tmp;
CREATE TABLE IF NOT EXISTS edu_dwd.dwd_fact_customer_relationship_tmp(
    id                          INT,
    create_date_time            STRING        COMMENT '创建时间',
    update_date_time            STRING        COMMENT '最后更新时间',
    deleted                     STRING        COMMENT '是否被删除（禁用）',
    customer_id                 INT           COMMENT '所属客户id',
    first_id                    INT           COMMENT '第一条客户关系id',
    belonger                    INT           COMMENT '归属人',
    belonger_name               STRING        COMMENT '归属人姓名',
    initial_belonger            INT           COMMENT '初始归属人',
    distribution_handler        INT           COMMENT '分配处理人',
    business_scrm_department_id INT           COMMENT '归属部门',
    last_visit_time             STRING        COMMENT '最后回访时间',
    next_visit_time             STRING        COMMENT '下次回访时间',
    origin_type                 STRING        COMMENT '数据来源',
    itcast_school_id            INT           COMMENT '校区Id',
    itcast_subject_id           INT           COMMENT '学科Id',
    intention_study_type        STRING        COMMENT '意向学习方式',
    anticipat_signup_date       STRING        COMMENT '预计报名时间',
    level                       STRING        COMMENT '客户级别',
    creator                     INT           COMMENT '创建人',
    current_creator             INT           COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                STRING        COMMENT '创建者姓名',
    origin_channel              STRING        COMMENT '来源渠道',
    COMMENT                     STRING,
    first_customer_clue_id      INT           COMMENT '第一条线索id',
    last_customer_clue_id       INT           COMMENT '最后一条线索id',
    process_state               STRING        COMMENT '处理状态',
    process_time                STRING        COMMENT '处理状态变动时间',
    payment_state               STRING        COMMENT '支付状态',
    payment_time                STRING        COMMENT '支付状态变动时间',
    signup_state                STRING        COMMENT '报名状态',
    signup_time                 STRING        COMMENT '报名时间',
    notice_state                STRING        COMMENT '通知状态',
    notice_time                 STRING        COMMENT '通知状态变动时间',
    lock_state                  STRING        COMMENT '锁定状态',
    lock_time                   STRING        COMMENT '锁定状态修改时间',
    itcast_clazz_id             INT           COMMENT '所属ems班级id',
    itcast_clazz_time           STRING        COMMENT '报班时间',
    payment_url                 STRING        COMMENT '付款链接',
    payment_url_time            STRING        COMMENT '支付链接生成时间',
    ems_student_id              INT           COMMENT 'ems的学生id',
    delete_reason               STRING        COMMENT '删除原因',
    deleter                     INT           COMMENT '删除人',
    deleter_name                STRING        COMMENT '删除人姓名',
    delete_time                 STRING        COMMENT '删除时间',
    course_id                   INT           COMMENT '课程ID',
    course_name                 STRING        COMMENT '课程名称',
    delete_comment              STRING        COMMENT '删除原因说明',
    close_state                 STRING        COMMENT '关闭装填',
    close_time                  STRING        COMMENT '关闭状态变动时间',
    appeal_id                   INT           COMMENT '申诉id',
    tenant                      INT           COMMENT '租户',
    total_fee                   DECIMAL(19)   COMMENT '报名费总金额',
    belonged                    INT           COMMENT '小周期归属人',
    belonged_time               STRING        COMMENT '归属时间',
    belonger_time               STRING        COMMENT '归属时间',
    transfer                    INT           COMMENT '转移人',
    transfer_time               STRING        COMMENT '转移时间',
    follow_type                 TINYINT       COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
    transfer_bxg_oa_account     STRING        COMMENT '转移到博学谷归属人OA账号',
    transfer_bxg_belonger_name  STRING        COMMENT '转移到博学谷归属人OA姓名',
    end_date                    STRING        COMMENT '拉链结束时间'
)
COMMENT '客户意向表'
PARTITIONED BY (start_date string) --拉链起始时间 也是表分区字段
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress'='SNAPPY');

insert overwrite table edu_dwd.dwd_fact_customer_relationship_tmp PARTITION (start_date)
select * from (
    select fcr.id,
           fcr.create_date_time,
           fcr.update_date_time,
           fcr.deleted,
           fcr.customer_id,
           fcr.first_id,
           fcr.belonger,
           fcr.belonger_name,
           fcr.initial_belonger,
           fcr.distribution_handler,
           fcr.business_scrm_department_id,
           fcr.last_visit_time,
           fcr.next_visit_time,
           fcr.origin_type,
           fcr.itcast_school_id,
           fcr.itcast_subject_id,
           fcr.intention_study_type,
           fcr.anticipat_signup_date,
           fcr.level,
           fcr.creator,
           fcr.current_creator,
           fcr.creator_name,
           fcr.origin_channel,
           fcr.comment,
           fcr.first_customer_clue_id,
           fcr.last_customer_clue_id,
           fcr.process_state,
           fcr.process_time,
           fcr.payment_state,
           fcr.payment_time,
           fcr.signup_state,
           fcr.signup_time,
           fcr.notice_state,
           fcr.notice_time,
           fcr.lock_state,
           fcr.lock_time,
           fcr.itcast_clazz_id,
           fcr.itcast_clazz_time,
           fcr.payment_url,
           fcr.payment_url_time,
           fcr.ems_student_id,
           fcr.delete_reason,
           fcr.deleter,
           fcr.deleter_name,
           fcr.delete_time,
           fcr.course_id,
           fcr.course_name,
           fcr.delete_comment,
           fcr.close_state,
           fcr.close_time,
           fcr.appeal_id,
           fcr.tenant,
           fcr.total_fee,
           fcr.belonged,
           fcr.belonged_time,
           fcr.belonger_time,
           fcr.transfer,
           fcr.transfer_time,
           fcr.follow_type,
           fcr.transfer_bxg_oa_account,
           fcr.transfer_bxg_belonger_name,
           if(cr.id is null or fcr.end_date < '9999-99-99', fcr.end_date, date_add(cr.dt, -1)) as end_date,
           start_date
    from edu_dwd.dwd_fact_customer_relationship fcr
     left join (select * from edu_ods.customer_relationship where dt = '${DT}') cr on fcr.id = cr.id
    union all
    --获取新增及更新数据
    select
        id,
        create_date_time,
        update_date_time,
        deleted,
        customer_id,
        first_id,
        belonger,
        belonger_name,
        initial_belonger,
        distribution_handler,
        business_scrm_department_id,
        last_visit_time,
        next_visit_time,
        origin_type,
        itcast_school_id,
        itcast_subject_id,
        intention_study_type,
        anticipat_signup_date,
        level,
        creator,
        current_creator,
        creator_name,
        origin_channel,
        comment,
        first_customer_clue_id,
        last_customer_clue_id,
        process_state,
        process_time,
        payment_state,
        payment_time,
        signup_state,
        signup_time,
        notice_state,
        notice_time,
        lock_state,
        lock_time,
        itcast_clazz_id,
        itcast_clazz_time,
        payment_url,
        payment_url_time,
        ems_student_id,
        delete_reason,
        deleter,
        deleter_name,
        delete_time,
        course_id,
        course_name,
        delete_comment,
        close_state,
        close_time,
        appeal_id,
        tenant,
        total_fee,
        belonged,
        belonged_time,
        belonger_time,
        transfer,
        transfer_time,
        follow_type,
        transfer_bxg_oa_account,
        transfer_bxg_belonger_name,
        '9999-99-99' as end_date,
        '${DT}' as start_date
    from edu_ods.customer_relationship
    where dt = '${DT}'
);

INSERT OVERWRITE TABLE edu_dwd.dwd_fact_customer_relationship partition (start_date)
SELECT * from edu_dwd.dwd_fact_customer_relationship_tmp;













